<?php

/*
  VoIP Wholesale 1.0
  ------------------
  Document   : module_mod_external
  Created on : Sep 25, 2011, 1:28:26 PM
  Author     : Milos Jovanovic <miloscode@gmail.com>
  Description:

 */
class Module_mod_external extends MY_Model {
    
    function __construct() {
        parent::__construct();
		
		$this->MODULE->load->model('module_mod_settings');		
		
		$this->MODULE->min_opening_delay = (int) $this->MODULE->module_mod_settings->get_setting_item_from_db('external_minimal_opening_delay');
		$this->MODULE->min_closing_delay = (int) $this->MODULE->module_mod_settings->get_setting_item_from_db('external_minimal_closing_delay');
		$this->MODULE->mysql_table = "numbering_plan_external";
    }
	
public function search_routes($post)
{

	$query = "SELECT * FROM `{$this->MODULE->mysql_table}` WHERE ";

	$sql = array();
	
	// country
	if($post['country_id'] != "none")
	{
		$sql[] = "`country_id` = '".$post['country_id']."'";
	}
	// route name
	if($post['route_name'] != "none")
	{
		$sql[] = "`name` = '".$post['route_name']."'";
	}	
	// dialling code
	$dialling_code = (int) $post['dialling_code'];
	if($dialling_code > 0)
	{
		$sql[] = "LEFT(`dialling_code`,".strlen($post['dialling_code']).") = '".$post['dialling_code']."'";
	}
	// status
	if($post['status'] != "none")
	{
		if($post['status'] == "all")
		{
			$sql[] = "(`status` = '0' OR `status` = '1' OR `status` = '2' OR `status` = '3')";
		}
		if($post['status'] == "active")
		{
			$sql[] = "(`status` = '1' OR `status` = '2')";
		}
		if($post['status'] == "open")
		{
			$sql[] = "`status` = '1'";
		}		
		if($post['status'] == "closed")
		{
			$sql[] = "`status` = '0'";
		}		
		if($post['status'] == "pending")
		{
			$sql[] = "(`status` = '2' OR `status` = '3')";
		}		
	}
	// type
	if($post['type'] != "none")
	{
		if($post['type'] == "all")
		{
			$sql[] = "(`type` = 'proper' OR `type` = 'mobile')";
		}
		else
		{
			$sql[] = "`type` = '".$post['type']."'";
		}
	}
	// max results
	if(@$post['num_of_results'] != "none" && count($sql) > 0)
	{
		$limit = " LIMIT ".$post['num_of_results'];
	}
	
	// prevent empty filters response
	if(count($sql) > 0){
	
		$sql = $query . implode(" AND ",$sql) . @$limit;
	
		//print $sql;die();

		$query = $this->db->query($sql);
	
		foreach($query->result_array() as $route)
		{
			$routes[] = $route;
		}
	}
	else
	{
		$routes = $this->PACKAGE->routing->numberingplan_external->get_active_routes();
	}
	return @$routes;
	
}	
		
public function add_new_route($post)
{
    /*
    * check if route with same name and dialcode exists in the database     
    */
    $route_name = $post['route_name'];
    
    /*
     * generate dialling codes array
     */
    $dialling_codes = explode(",",$post['dialling_codes']);    

	
    /*
     *prevent wrong variable errors
     */
     $dialling_codes_array = array();
     
     if(!is_array($dialling_codes))
     {
      $dialling_codes = array();
     }
    
    
    /*
     * convert dialling codes into INTEGERS to prevent bad database search
	 * and get countries for every single dialling code
     */
    foreach($dialling_codes as $dialling_code)
    {
        //$dialling_code = (int)$dialling_code;  -- integers do not support very large numbers!!!
        
        if($dialling_code != 0)
		{
            $dialling_codes_array[] = $dialling_code;        
			
			// create countries array by using entered prefixes
			$country = $this->countries->get_country_as_array_from_prefix($dialling_code);
			
			if($country != false)
			{				
				$dialling_codes_countries_array[] = $country['country_name'];
			}
			else
			{
				// generate array of dialcodes that do not have country
				$dialling_codes_no_country[] = $dialling_code;
			}
        }
    }	

	
    /*  ############################
     *  VERIFY ENTERED DATA
	 *  ############################
     */


	// get posted dates
	$date_from = $post['date_from'];
	$date_to = $post['date_to'];
	$date_from_unix = strtotime($date_from);
	$allowed_date_from = time() + (86400 * ($this->MODULE->min_opening_delay - 1));	
	 
     // empty route name
     if(strlen($route_name) < 1)
     {
          $response = array(
                            "empty_route_name",
                            lang('msg_empty_route_name')
                           );
     }
	 // empty start date
	 else if(empty($date_from))
	 {
          $response = array(
                            "empty_start_date",
                            lang('msg_empty_start_date')
                           );	 
	 }
	 // starting date too low
	 else if($date_from_unix < $allowed_date_from)
	 {
          $response = array(
                            "start_date_too_low",
                            lang('msg_low_start_date') . " + " . $this->MODULE->min_opening_delay . "&nbsp;" . lang('term_days')
                           );	
	 }	 
     // dialling codes array contains letters
     else if(preg_match('/[a-zA-Z]/', $post['dialling_codes']))
     {
          $response = array(
                            "letters_in_dialcodes",
                            lang('msg_letters_in_dialcodes')
                           );
     }
     // empty dialcodes
     else if(count($dialling_codes_array) == 0)
     {
          $response = array(
                            "empty_dialcodes",
                            lang('msg_empty_dialling_code_s')
                           );
     }
     //dialcodes do not belong to the same country
     else if(@count(array_count_values($dialling_codes_countries_array)) > 1)
     {
		  $text = "<ul>";
		  foreach(array_count_values($dialling_codes_countries_array) as $country => $frequency)
		  {
			$text .= "<li>".$country . " (" .$frequency .") </li>";
		  }
		  $text .= "</ul>";
	 
          $response = array(
                            "dialcodes_from_different_countries",
                            lang('msg_dialling_codes_from_different_countries')."<br />".$text
                           );
     }	 
     // dialcode does not belong to any country
     else if(@count($dialling_codes_countries_array) != count($dialling_codes_array))
     {
		  $text = "<ul>";
		  foreach($dialling_codes_no_country as $code)
		  {
			$text .= "<li>".$code."</li>";
		  }
		  $text .= "</ul>";
	 
          $response = array(
                            "dialcodes_do_not_have_country",
                            lang('msg_dialling_codes_do_not_have_country')."<br />".$text
                           );
     }	 
	 
	 // duplicates in dialling codes field
	 else if(count($dialling_codes_array) != count(array_unique($dialling_codes_array)))
	 {
          $response = array(
                            "duplicate_entries_in_dialcodes",
                            lang('msg_duplicate_entries_in_dialcodes')
                           );	 	 
	 }
	 
	 // empty route type
	 else if($post['type'] == "choose")
	 {
          $response = array(
                            "empty_type",
                            lang('msg_please_choose_type')
                           );	 
	 }
	 
    /*  #########################################################
     *  ENTERED DATA SEEM PROPER - CHECK FOR DATABASE OVERLAPPING
	 *  #########################################################
     */	 
     else
     {

		$dialling_codes_sql = implode("' OR `dialling_code` ='",$dialling_codes_array);
    
		$sql = "SELECT * FROM `{$this->MODULE->mysql_table}` WHERE `name` = '{$route_name}' AND (`dialling_code` = '{$dialling_codes_sql}') AND (`status`='1' OR `status` ='2' OR `status` ='3')";

		$query = $this->db->query($sql);

		/* *************************************************************************
		 * PREVENT ROUTES WITH THE SAME NAME AND THE SAME DIALLING CODE TO BE STORED
		 * *************************************************************************
		 */
		if($query->num_rows() > 0)
		{
			$existing_routes = "<ul>";
			foreach($query->result_array() as $route)
			{
				$existing_routes .= "<li>".$route['name'] . "[" .$route['dialling_code']."]"."</li>";
			}
			$existing_routes .= "</ul>";
		
			$text = "<strong>".lang('term_routes_exist')."</strong> ".lang('msg_following_routes_already_exist') . "<br />" . $existing_routes;
		
			$response = array(
							"routes_already_exist",
							$text
							);
		}
		/* ***************************************************************************************
		 * PREVENT DIFFERENT DIALLING CODES TO OVERLAP THE DATES
		 * ****************************************************************************************
		 */
		else
		{			
			// check every single prefix for date range overlapping
			$overlapping_prefixes = array();
			$pending_close_prefixes = array();
			$pending_open_prefixes = array();
			
			foreach($dialling_codes_array as $dialling_code)
			{
				// active prefixes - status 1(active)
				$sql1 = "SELECT * FROM `{$this->MODULE->mysql_table}` WHERE `dialling_code` = '{$dialling_code}' AND ((DATE(`start_date`) <= '{$date_from}' AND DATE(`exp_date`) >= '{$date_to}') OR (`status` ='1'))";

				$query1 = $this->db->query($sql1);	

				if($query1->num_rows() > 0)
				{
					foreach($query1->result_array() as $route)
					{
						$overlapping_prefixes[] = $route;
					}
				}
				
				// pending close prefixes - status 3(pending close)
				$sql2 = "SELECT * FROM `{$this->MODULE->mysql_table}` WHERE `dialling_code` ='{$dialling_code}' AND `status` = '3' AND DATE(`exp_date`) > '{$date_from}'";
				
				$query2 = $this->db->query($sql2);
				
				if($query2->num_rows() > 0)
				{
					foreach($query2->result_array() as $route)
					{
						$pending_close_prefixes[] = $route;
					}					
				}

				// pending open prefixes - status 2(pending open)
				$sql3 = "SELECT * FROM `{$this->MODULE->mysql_table}` WHERE `dialling_code` ='{$dialling_code}' AND `status` = '2'";
				
				$query3 = $this->db->query($sql3);
				
				if($query3->num_rows() > 0)
				{
					foreach($query3->result_array() as $route)
					{
						$pending_open_prefixes[] = $route;
					}					
				}				
			}	
			
			// there are some routes pending open date
			if (count($pending_open_prefixes) > 0)
			{
				$pending_open_routes = "<ul>";
				foreach($pending_open_prefixes as $route)
				{
					// generate dates for showing in a response
					$date1 = strtotime($route['start_date']);
					$date1 = date( 'Y/m/d', $date1 );					
					$date2 = strtotime($route['exp_date']);
					$date2 = date( 'Y/m/d', $date2 );					
					
					$pending_open_routes .= "<li><b>".$route['name']." [".$route['dialling_code']."] </b>(". $date1 ." - ". $date2 .")</li>";
				}
				$pending_open_routes.="</ul>";
				
				$text = "<strong>".lang('term_pending_open_routes')."</strong> ".lang('msg_following_routes_pending_open') . "<br />" . $pending_open_routes;
			
				$response = array(
									"pending_open_routes",
									$text
								  );			
			}	
			// there are some routes pending close date
			elseif (count($pending_close_prefixes) > 0)
			{
				$pending_close_routes = "<ul>";
				foreach($pending_close_prefixes as $route)
				{
					// generate dates for showing in a response
					$date1 = strtotime($route['start_date']);
					$date1 = date( 'Y/m/d', $date1 );					
					$date2 = strtotime($route['exp_date']);
					$date2 = date( 'Y/m/d', $date2 );					
					
					$pending_close_routes .= "<li><b>".$route['name']." [".$route['dialling_code']."] </b>(". $date1 ." - ". $date2 .")</li>";
				}
				$pending_close_routes.="</ul>";
				
				$text = "<strong>".lang('term_pending_close_routes')."</strong> ".lang('msg_following_routes_pending_close') . "<br />" . $pending_close_routes;
			
				$response = array(
									"pending_close_routes",
									$text
								  );			
			}			
			// there are some overlapping prefixes
			elseif(count($overlapping_prefixes) > 0)
			{
				$overlapping_routes = "<ul>";
				foreach($overlapping_prefixes as $route)
				{
					// generate dates for showing in a response
					$date1 = strtotime($route['start_date']);
					$date1 = date( 'Y/m/d', $date1 );					
					$date2 = strtotime($route['exp_date']);
					$date2 = date( 'Y/m/d', $date2 );					
					
					$overlapping_routes .= "<li><b>".$route['name']." [".$route['dialling_code']."] </b>(". $date1 ." - ". $date2 .")</li>";
				}
				$overlapping_routes.="</ul>";
				
				$text = "<strong>".lang('term_prefixes_overlap')."</strong> ".lang('msg_following_prefixes_overlap') . "<br />" . $overlapping_routes;
			
				$response = array(
									"prefixes_overlap",
									$text
								  );
			}			
			else
			{
				// there are no conflict prefixes
				// perform database storing
				
				if(@$post['confirm'] == "true")
				{
					foreach($dialling_codes_array as $dialling_code)
					{
					$country = $this->countries->get_country_as_array_from_prefix($dialling_code);
					
					$insert['name'] = $route_name;
					$insert['type'] = $post['type'];
					$insert['start_date'] = $date_from;
					$insert['exp_date'] = $date_to;
					$insert['dialling_code'] = $dialling_code;
					$insert['status'] = 2; //pending open
					$insert['country_id'] = $country['id'];
					
					$this->db->insert($this->MODULE->mysql_table,$insert);
					}
					$response = array(
										"true",
										lang('msg_new_route_added')
									  );
				}
				else
				{
					$text = lang('text_following_routes_will_be_added');
					$text .= "<ul>";
					foreach($dialling_codes_array as $dialling_code)
					{
						$country = $this->countries->get_country_as_array_from_prefix($dialling_code);
						$text .= "<li><strong>".$route_name."&nbsp; [".$dialling_code."]</strong>&nbsp; :: ".$post['type'];
					}
					$text .= "</ul>";
					
					$text .= "<br />";
					
					$text .= "<hr/>";
					
					$text .= "<br />";
					
					$text .= "<div class='ui-state-default ui-corner-all'><strong>".lang('text_country')."</strong> " . $country['country_name'] ."<br />";
					$text .= "<strong>".lang('text_start_date'). "</strong> " . $date_from ."<br />";
					$text .= "<strong>".lang('text_exp_date'). "</strong> " . $date_to ."<br />";
					
					$text .= "</div>";
					
					$response = array(
									"pending_confirmation",
									lang('msg_pending_confirmation'),
									$text
								  );
				}
			}
		}

    }
    
    $response = implode("|",$response);
          
    return $response;
}

//-------------------
public function close_route($post)
{
	$date_from = $post['date_from'];
	$date_to = $post['date_to'];
	$date_to_unix = strtotime($date_to);
	$allowed_date_to = time() + (86400 * ($this->MODULE->min_closing_delay - 1));	
	
	$route_id = $post['route_id'];
	 
	 // empty start date
	if(empty($date_to))
	 {
          $response = array(
                            "empty_end_date",
                            lang('msg_empty_end_date')
                           );	 
	 }
	 // starting date too low
	 else if($date_to_unix < $allowed_date_to)
	 {
          $response = array(
                            "end_date_too_low",
                            lang('msg_low_start_date') . " + " . $this->MODULE->min_opening_delay . "&nbsp;" . lang('term_days')
                           );	
	 }	 	 
    /*  #########################################################
     *  ENTERED DATA SEEM PROPER - CHECK FOR DATABASE OVERLAPPING
	 *  #########################################################
     */	 
     else
     {					
				// perform database update
				
				if(@$post['confirm'] == "true")
				{									
					$update['exp_date'] = $date_to;
					$update['status'] = 3; //pending close
					
					$this->db->where('id',$route_id)->update($this->MODULE->mysql_table,$update);
					
					$response = array(
										"true",
										lang('msg_route_closed')
									  );
				}
				else
				{
					$query = $this->db->where('id',$route_id)->get($this->MODULE->mysql_table);
					
					$route = $query->row_array();					
				
					$country = $this->countries->get_country_as_array_from_prefix($route['dialling_code']);
				
					$text = lang('text_following_route_will_be_closed');
					$text .= "<ul>";
					$text .= "<li><strong>".$route['name']."&nbsp; [".$route['dialling_code']."]</strong>&nbsp; :: ".$route['type'];
					$text .= "</ul>";
					
					$text .= "<br />";
					
					$text .= "<hr/>";
					
					$text .= "<br />";
					
					$text .= "<div class='ui-state-default ui-corner-all'><strong>".lang('text_country')."</strong> " . $country['country_name'] ."<br />";
					$text .= "<strong>".lang('text_start_date'). "</strong> " . $date_from ."<br />";
					$text .= "<strong>".lang('text_exp_date'). "</strong> " . $date_to ."<br />";
					
					$text .= "</div>";
					
					$response = array(
									"pending_confirmation",
									lang('msg_pending_confirmation'),
									$text
								  );
				}
			
		

    }
    
    $response = implode("|",$response);
          
    return $response;
}

}
?>
